﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Windows.Forms;
using System.Collections;

namespace WindowsFormsApplication1
{
    class EmployeeController
    {
        private DBConnector connector;

        public EmployeeController()
        {
            connector = new DBConnector();
        }

        public DataTable getNames()
        {
            string query = "SELECT lastName , firstName FROM employee ORDER BY lastName, firstName;";

            if (connector.openConnection())
            {
                MySqlDataAdapter dataAdapter = new MySqlDataAdapter(query, connector.getConnection());
                MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(dataAdapter);
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);
                connector.closeConnection();
                return dataTable;
            }
            else
            {
                return null;
            }
        }

        public int getEmployeeCode(String firstname, String lastname)
        {
            int employeeCode = -1;
            string query = "SELECT employeeCode from employee WHERE firstName = \'" + firstname + "\' AND lastName = \'" + lastname + "\'";
            if (connector.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                MySqlDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.Read())
                    employeeCode = (int)dataReader["employeeCode"];
                dataReader.Close();
                connector.closeConnection();
            }
            return employeeCode;
        }

        public List<int> getAllEmployeeCode()
        {
            List<int> list = new List<int>();
            string query = "SELECT employeeCode from employee WHERE employeeStatus = 'Active'";
            if (connector.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                MySqlDataReader dataReader = cmd.ExecuteReader();
                while (dataReader.Read())
                    list.Add((int)dataReader["employeeCode"]);
                dataReader.Close();
                connector.closeConnection();
                return list;
            }
            return null;
        }

        public List<string> getListNames()
        {
            List<string> list = new List<string>();
            string first;
            string last;
            string query = "SELECT lastName , firstName from employee WHERE employeeStatus = 'Active' ORDER BY lastName";
            if (connector.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                MySqlDataReader dataReader = cmd.ExecuteReader();
                while (dataReader.Read())
                {
                    last = dataReader["lastName"]+"";
                    first = dataReader["firstName"]+"";
                    list.Add(last+","+first);
                }
                dataReader.Close();
                connector.closeConnection();
                return list;
            }
            return null;
        }

        public Employee getInformation(string firstName, string lastName)
        {
            string query = "SELECT lastName, firstName, salary, payingBasis, workHours, position, contractStart, contractEnd, SSS, PhilHealth, debt, employeeStatus, bonusesDeductions  FROM employee WHERE lastName = \'" + lastName + "\' and firstName = \'" + firstName +"\';";
            Employee employee = new Employee();

            if (connector.openConnection())
            {
                MySqlDataAdapter dataAdapter = new MySqlDataAdapter(query, connector.getConnection());
                MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(dataAdapter);
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);
                connector.closeConnection();
                if (dataTable.Rows.Count != 0)
                {
                    employee.lastName = dataTable.Rows[0][0].ToString();
                    employee.firstName = dataTable.Rows[0][1].ToString();
                    employee.salary = double.Parse(dataTable.Rows[0][2].ToString());
                    employee.payingBasis = (dataTable.Rows[0][3].ToString() == "Daily") ? 0 : 1;
                    employee.workHours = int.Parse(dataTable.Rows[0][4].ToString());
                    employee.position = (dataTable.Rows[0][5].ToString() == "Employee") ? 0 : (dataTable.Rows[0][5].ToString() == "Captain") ? 1 : (dataTable.Rows[0][5].ToString() == "Supervisor") ? 2 : 3;
                    employee.contractStart = dataTable.Rows[0][6].ToString();
                    employee.contractEnd = dataTable.Rows[0][7].ToString();
                    employee.SSS = double.Parse(dataTable.Rows[0][8].ToString());
                    employee.PhilHealth = double.Parse(dataTable.Rows[0][9].ToString());
                    employee.debt = double.Parse(dataTable.Rows[0][10].ToString());
                    employee.employeeStatus = (dataTable.Rows[0][11].ToString() == "Active") ? 0 : 1;
                    employee.bonusesDeductions = double.Parse(dataTable.Rows[0][12].ToString());
                }
                return employee;
                
            }
            else
            {
                return null;
            }
        }
        public bool addEmployee(Employee employee)
        {
            string status = (employee.employeeStatus==0) ? "Active" : "Inactive";
            string payingBasis = (employee.payingBasis==0) ? "Daily" : "Monthly";
            string position = (employee.position==0) ? "Employee" : (employee.position==1) ? "Captain" : (employee.position==2) ? "Supervisor" : "Guard";
            string query = "INSERT INTO employee(lastName, firstName, salary, payingBasis, workHours, position, contractStart, contractEnd, SSS, PhilHealth, debt, employeeStatus, bonusesDeductions) VALUES (\'" + employee.lastName + "\', \'" + employee.firstName + "\', " + employee.salary + ", \'" + payingBasis + "\', " + employee.workHours + ", \'" + position + "\', \'" + employee.contractStart + "\', \'" + employee.contractEnd + "\', " + employee.SSS + ", " + employee.PhilHealth + ", " + employee.debt + ", \'" + status + "\', " + employee.bonusesDeductions + ");";   

            if (connector.openConnection())
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                    cmd.ExecuteNonQuery();
                    connector.closeConnection();
                    return true;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Cannot add employee", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return false;
                }
            }

            else
                return false;

        }
        public bool editEmployee(Employee employee, string lastName, string firstName)
        {
            string status = (employee.employeeStatus == 0) ? "Active" : "Inactive";
            string payingBasis = (employee.payingBasis == 0) ? "Daily" : "Monthly";
            string position = (employee.position == 0) ? "Employee" : (employee.position == 1) ? "Captain" : (employee.position == 2) ? "Supervisor" : "Guard";
            string query = "UPDATE employee set lastName = \'" + employee.lastName + "\', firstName = \'" + employee.firstName + "\', salary = " + employee.salary + ", payingBasis = \'" + payingBasis + "\', workHours = " + employee.workHours + ", position = \'" + position + "\', contractStart = \'" + employee.contractStart + "\', contractEnd = \'" + employee.contractEnd + "\', SSS = " + employee.SSS + ", PhilHealth = " + employee.PhilHealth + ", debt = " + employee.debt + ", employeeStatus = \'" + status + "\', bonusesDeductions = " + employee.bonusesDeductions + " WHERE lastName = \'" + lastName + "\' and firstName = \'" + firstName + "\';"; 

            if (connector.openConnection())
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.CommandText = query;
                    cmd.Connection = connector.getConnection();
                    cmd.ExecuteNonQuery();
                    connector.closeConnection();
                    return true;
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show("The employee you are trying to edit does not exist.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return false;
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                    return false;
                }
            }
            else
                return false;
        }

        public bool deleteEmployee(string firstName, string lastName)
        {
            string query = "DELETE FROM employee WHERE firstName = \'" + firstName + "\' and lastName = \'" + lastName + "\';";
            if (connector.openConnection())
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                    cmd.ExecuteNonQuery();
                    connector.closeConnection();
                    return true;
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show("Selected employee cannot be deleted.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return false;
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                    return false;
                }
            }
            return false;
        }

        public int countEmployees()
        {
            int count = 0;
            string query = "SELECT COUNT(employeeCode) as 'count' from employee WHERE employeeStatus = 'Active'";
            if (connector.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                MySqlDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.Read())
                    count = int.Parse(dataReader["count"]+"");
                dataReader.Close();
                connector.closeConnection();
            }
            return count;
        }

        public int getPosition(int employeeCode)
        {
            String position = "";
            string query = "SELECT position from employee where employeeCode = " + employeeCode;
            if (connector.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                MySqlDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.Read())
                    position = dataReader[0]+"";
                dataReader.Close();
                connector.closeConnection();
            }

            if (position.Equals("Supervisor", StringComparison.CurrentCultureIgnoreCase))
                return 1;
            else if (position.Equals("Captain", StringComparison.CurrentCultureIgnoreCase))
                return 2;
            else if (position.Equals("Guard", StringComparison.CurrentCultureIgnoreCase))
                return 3;
            else
                return 0;
        }

        public int countSuperGuard()
        {
            int count = 0;
            string query = "SELECT COUNT(employeeCode) from employee where position = 'Supervisor' OR position = 'Guard' AND employeeStatus = 'Active'";
            if (connector.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                MySqlDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.Read())
                    count = int.Parse(dataReader[0]+"");
                dataReader.Close();
                connector.closeConnection();
            }
            return count;
        }

        public int countSuperCaptain()
        {
            int count = 0;
            string query = "SELECT COUNT(employeeCode) from employee where position = 'Supervisor' OR position = 'Captain' AND employeeStatus = 'Active'";
            if (connector.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                MySqlDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.Read())
                    count = int.Parse(dataReader[0]+"");
                dataReader.Close();
                connector.closeConnection();
            }
            return count;
        }

        public bool resetAll()
        {
            List<int> list = getAllEmployeeCode();
            if (connector.openConnection())
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand();
                    foreach (int code in list)
                    {
                        string query = "UPDATE employee SET debt = 0, bonusesDeductions =0 where employeeCode = " + code;
                        cmd.CommandText = query;
                        cmd.Connection = connector.getConnection();
                        cmd.ExecuteNonQuery();
                    }
                    connector.closeConnection();
                    return true;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    return false;
                }
            }
            return false;            
        }
    }
}
